from openpyxl.utils import range_boundaries
from openpyxl.worksheet.worksheet import Worksheet

from .schema import (
    SummaryTable,
    START_ROW_OF_DATA,
    ProductDetail,
    SummaryProduct,
    BidTable,
    NO_UNIT_PRICE_STATUS,
    ExceptionOfferDetail,
    ExceptionOffer,
    BidProduct,
    OfferDetail,
    Offer,
)


def import_products_from_tender(sheet: Worksheet) -> SummaryTable:
    table = SummaryTable(table_name=sheet.title)

    for row in sheet.iter_rows(
        min_row=START_ROW_OF_DATA, max_row=sheet.max_row, max_col=20
    ):
        # 基本检查：序号和产品名称必须有值
        if not (row[0].value and row[2].value):
            continue

        # 特殊处理：当规格型号为空时，使用产品名称作为规格型号
        spec = row[3].value if (len(row) > 3 and row[3].value) else row[2].value

        detail = ProductDetail(
            order_num=row[0].value,
            code=row[1].value if len(row) > 1 and row[1].value else None,
            name=row[2].value,
            spec=spec,  # 使用处理后的规格型号
            unit=row[4].value if len(row) > 4 and row[4].value else None,
            required_brand=row[6].value if len(row) > 6 else None,
        )

        try:
            product = SummaryProduct(
                key_name=detail.name,
                key_spec=detail.spec,  # 这里spec可能等于name
                detail=detail,
                offers=[],
            )
            table.products.append(product)
        except Exception as e:
            print(f"Error creating product for row {row[0].row}: {e}")

    return table


def import_products_from_bid(
    sheet: Worksheet,
    supplier: str,
    is_bargaining: bool = False,
    is_special_format: bool = False,
) -> BidTable:
    table = BidTable(table_name=sheet.title, supplier=supplier)

    for row in sheet.iter_rows(
        min_row=START_ROW_OF_DATA, max_row=sheet.max_row, max_col=20
    ):
        # 基本检查：序号和产品名称必须有值
        if not (row[0].value and row[2].value):
            continue

        # 特殊处理：当规格型号为空时，使用产品名称作为规格型号
        spec = row[3].value if (len(row) > 3 and row[3].value) else row[2].value

        detail = ProductDetail(
            order_num=row[0].value,
            code=row[1].value if len(row) > 1 and row[1].value else None,
            name=row[2].value,
            spec=spec,  # 使用处理后的规格型号
            unit=row[4].value if len(row) > 4 and row[4].value else None,
            required_brand=None,  # 投标表通常不包含要求品牌
        )

        # 报价列处理（保持不变）
        value = row[5].value if len(row) > 5 else None

        if value in NO_UNIT_PRICE_STATUS:
            exception_detail = ExceptionOfferDetail(
                unit_price=value,
                brand=row[6].value if len(row) > 6 else "",
            )
            product = BidProduct(
                key_name=detail.name,
                key_spec=detail.spec,  # 这里spec可能等于name
                detail=detail,
                offer=ExceptionOffer(
                    status=0,
                    supplier=supplier,
                    key_price=value,
                    is_bargaining=is_bargaining,
                    detail=exception_detail,
                ),
            )
        elif not isinstance(value, (int, float)):
            # 非法报价处理
            product = BidProduct(
                key_name=detail.name,
                key_spec=detail.spec,  # 这里spec可能等于name
                detail=detail,
                offer=ExceptionOffer(
                    status=-1,
                    supplier=supplier,
                    key_price=value,
                    is_bargaining=is_bargaining,
                    detail=ExceptionOfferDetail(
                        unit_price=value,
                        brand=row[6].value if len(row) > 6 else "",
                    ),
                ),
            )
        else:
            # 正常报价处理
            product = BidProduct(
                key_name=detail.name,
                key_spec=detail.spec,  # 这里spec可能等于name
                detail=detail,
                offer=Offer(
                    supplier=supplier,
                    key_price=value,
                    is_bargaining=is_bargaining,
                    detail=OfferDetail(
                        unit_price=value,
                        brand=row[6].value if len(row) > 6 else "",
                    ),
                ),
            )

        table.products.append(product)

    return table


def unmerge_and_fill_cells(worksheet):
    """拆分合并单元格并填充值"""
    merged_ranges = list(worksheet.merged_cells.ranges)
    for merged_range in merged_ranges:
        min_col, min_row, max_col, max_row = range_boundaries(str(merged_range))
        merged_value = worksheet.cell(row=min_row, column=min_col).value
        worksheet.unmerge_cells(str(merged_range))
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                worksheet.cell(row=row, column=col).value = merged_value
